# File Name: createData.py
# Author: Daniel Squires
# Created: 10/11/12
# This script writes sql scripting to load data into the database

import random
import string

vendors = {'00000':'Moobys','11111':'Kwik-E-Mart','22222':'Torchwood','33333':'Mos Eisley Cantina','44444':'Graystone Industries','55555':'Part R Us','66666':'R Soles','77777':'Corleone Olive Oil','88888':'Moes Tavern','99999':'Bluth Company'}

users = ['Mark Blunderbutt', 'Tvs Frank', 'Clayton Forrestor', 'Hefty Smurf', 'CrowT Robot', 'Tom Servo', 'Ozzy Osborne', 'Big Bird', 'Howard Duck', 'Joel Robinson']
passChars = string.ascii_uppercase + string.digits + string.ascii_lowercase
partsChars = string.ascii_uppercase + string.digits
fileName = "marssneers.dat"
partNameList = []
finishList = []

def createUsers(mode):
    f = open(fileName, mode)
    f.write("/* user_id, first_name, last_name, login, password */\n")
    for user in users:
        userID = ''
        if users.index(user) < 10: # users 0 - 9
            userID += ("0" * 4) + str(users.index(user))
        else: # users 10 - 99
            userID += ("0" * 3) + str(users.index(user))
        
        fName, lName = user.split()
        login = fName.lower() + "-" + lName.lower() + "@marssneers.com"
        password = ''.join(random.choice(passChars) for x in range(8))
        f.write("INSERT INTO Users VALUES('" + userID + "', '" + fName + "', '" + lName + "', '" + login + "', '" + password + "');\n")
    f.write("\n")
    f.close()

def createParts(mode):
    partNumbers = [] # list to hold used part numbers
    f = open(fileName, mode)
    
    f.write("/* part_id, vendor_id, cost, part_description, inventory */\n")
    for vendorID, name in vendors.items():
        numOfParts = random.randint(10,99) # for each vendor create a random amount of parts
        for x in range(numOfParts):
            
            # Create random unique part numbers
            partNumber = ''.join(random.choice(partsChars) for x in range(10))
            while partNumber in partNumbers:
                partNumber = ''.join(random.choice(partsChars) for x in range(10))
            
            cost = str(round(random.uniform(0, 200), 2)) # Cost of part
            inventory = str(random.randint(0, 100)) # Amount of inventory on hand
            partDesc = "Vendor: " + vendorID + ". Part number:" + partNumber
            partNameList.append(partNumber)
            f.write("INSERT INTO Parts VALUES('" + partNumber + "', '" + vendorID + "', '" + cost + "', '" +  partDesc + "', '" + inventory + "');\n")
            
    f.write("\n")
    f.close()

def createVendors(mode):
    f = open(fileName, mode)
    f.write("/* vender_id, company_name */\n")
    for vendorID, name in vendors.items():
        f.write("INSERT INTO Vendors VALUES('" + vendorID + "', '" + name + "');\n")
    f.write("\n")
    f.close()

def createFinishedGoods(mode):
    f = open(fileName, mode)
    f.write("/* vender_id, company_name */\n")
    for i in range(50):
        fg_id = ""
        if i < 10:
            fg_id = '0000'
        else:
            fg_id = '000'
        fg_id = fg_id + str(i)
        f.write("INSERT INTO Finished_Goods VALUES('" + fg_id + "', 'Widget " + str(i) + "');\n")
        finishList.append(fg_id)
    f.write("\n")
    f.close()

    
def createPartsRequired(mode):
    xs = range(1,len(finishList))
    ys = range(1,len(partNameList))
    f = open(fileName,mode)
    f.write("/* fg_id, part_id,quantiy */\n")
    for (i,x) in enumerate(xs):
        for y in ys[(i*3):(i*3+3)]:
            f.write("INSERT INTO Parts_Required VALUES('" + finishList[x] + "', '" + partNameList[y] + "', '" + '5' + "');\n")
    f.write("\n")
    f.close()
        
createUsers('w')
createVendors('a')
createFinishedGoods('a')
createParts('a')
createPartsRequired('a')

